Understanding One-to-Many Relationships in MySQL

Course: MySQL Training

Topic: Understanding One-to-Many Relationships

Course Link: MySQL Training Course at JustAcademy

Demo Link: Register for a Course Demo

Website: JustAcademy

1. Introduction to One-to-Many Relationships

A one-to-many relationship is one of the most common relationships used in relational database design. It describes a situation in which one record in a table can be associated with multiple records in another table, while each record in the second table is associated with one record in the first table.

For example, one customer can place many orders, but each order generally belongs to one customer. Similarly, one department can have many employees, while each employee belongs to one department.

In MySQL, one-to-many relationships are typically implemented using a primary key in the parent table and a foreign key in the child table.

Example: Customers and Orders

• One customer can place several orders.
• Each order belongs to a particular customer.
• The customer record is stored in the customers table.
• The order records are stored in the orders table.
• The customer_id column in orders references the primary key in customers.

customers
-------------------------
customer_id | customer_name
1           | Rahul
2           | Priya

orders
--------------------------------
order_id | customer_id | amount
101      | 1           | 1500.00
102      | 1           | 2500.00
103      | 2           | 900.00

In this example, Rahul has two orders and Priya has one. The relationship is one-to-many because one customer can be linked to multiple orders.

2. Learning Objectives

After completing this topic, you should be able to:

• Define a one-to-many relationship in relational databases.
• Identify parent and child tables.
• Understand primary keys and foreign keys.
• Create one-to-many relationships using MySQL constraints.
• Insert and retrieve related records.
• Use joins to display data from related tables.
• Understand foreign key rules and referential integrity.
• Identify common relationship-design mistakes.
• Apply one-to-many relationships in real-world database projects.

3. Important Terms and Definitions

4. What Is a Parent Table?

A parent table contains the record that other records refer to. In a one-to-many relationship, the parent table usually has a primary key that identifies each parent record uniquely.

Consider a departments table. Each department has a unique department ID. Multiple employees can belong to the same department.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

Sample data:

INSERT INTO departments (department_id, department_name)
VALUES
(1, 'IT'),
(2, 'Human Resources'),
(3, 'Finance');

Here, departments is the parent table because its records can be referenced by employee records.

5. What Is a Child Table?

A child table stores records that are associated with parent records. It contains a foreign key referencing the parent table's primary key or another eligible referenced key.

For example, the employees table can contain a department_id foreign key. Multiple employees may share the same department ID.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT,
    CONSTRAINT fk_employees_department
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
);

In this design:

• employee_id uniquely identifies each employee.
• department_id identifies the employee's department.
• The foreign key ensures that a non-NULL department ID must match an existing department.
• Because department_id is nullable, an employee may temporarily have no department assigned.

6. How a One-to-Many Relationship Works

The relationship works by storing the parent record's key in each associated child record. The parent does not need to store a list of child IDs.

Relationship Structure

departments
------------
department_id (PK)
department_name

          1
          |
          | has many
          |
          N

employees
------------
employee_id (PK)
employee_name
department_id (FK)

PK means primary key, and FK means foreign key.

Suppose the IT department has three employees. All three employee records can contain department_id = 1. This repeated foreign key value is expected and is what allows multiple employees to reference the same department.

Department 1 is associated with Amit, Neha, and Sameer. Department 2 is associated with Priya. The employee table contains the foreign key that implements the relationship.

7. Primary Keys in One-to-Many Relationships

A primary key uniquely identifies each row in a table. It prevents two records from having the same primary key value and does not allow NULL values.

In a one-to-many relationship, the parent table's primary key is referenced by the foreign key in the child table.

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE
);

Each customer receives a unique customer_id. The orders table can use this value to identify which customer placed each order.

8. Foreign Keys in One-to-Many Relationships

A foreign key is a database constraint that maintains a valid connection between related tables. In MySQL, foreign keys are commonly used with the InnoDB storage engine.

Foreign Key Syntax

FOREIGN KEY (child_column)
REFERENCES parent_table(parent_column)

Example

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
) ENGINE = InnoDB;

The constraint fk_orders_customer ensures that every non-NULL customer_id in the orders table references an existing customer. Since the column is declared NOT NULL, each order must have a customer.

9. Creating a Complete One-to-Many Relationship in MySQL

Follow these steps to create a customer-order relationship from scratch.

Step 1: Create a Database

CREATE DATABASE relationship_demo;
USE relationship_demo;

Step 2: Create the Parent Table

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    city VARCHAR(80)
) ENGINE = InnoDB;

Step 3: Create the Child Table

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
) ENGINE = InnoDB;

Step 4: Insert Parent Records

INSERT INTO customers (customer_name, email, city)
VALUES
('Rahul Sharma', '[email protected]', 'Mumbai'),
('Priya Patel', '[email protected]', 'Pune'),
('Amit Verma', '[email protected]', 'Nashik');

Step 5: Insert Child Records

INSERT INTO orders (customer_id, order_date, total_amount)
VALUES
(1, '2026-10-01', 1500.00),
(1, '2026-10-05', 2500.00),
(2, '2026-10-07', 900.00),
(1, '2026-10-10', 1200.00),
(3, '2026-10-12', 3000.00);

Customer 1 has three orders, customer 2 has one order, and customer 3 has one order. The same customer ID can appear in multiple rows in the child table.

Step 6: Verify the Records

SELECT * FROM customers;
SELECT * FROM orders;

Use these queries to inspect the parent and child tables separately before joining them.

10. Retrieving Related Records Using INNER JOIN

An INNER JOIN returns rows where the join condition matches records in both tables. It is useful when you want to display orders together with the customers who placed them.

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.order_date,
    o.total_amount
FROM customers AS c
INNER JOIN orders AS o
    ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_date;

The query matches each order's customer_id with the corresponding customer's primary key. A customer with no orders will not appear in this result because an INNER JOIN requires a matching order.

11. Retrieving All Parent Records Using LEFT JOIN

A LEFT JOIN returns every row from the left table and matching rows from the right table. If a customer has no orders, the customer still appears, and the order columns contain NULL.

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.total_amount
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_id;

Use a LEFT JOIN when the report must include every customer, including those who have not placed an order.

12. Finding Parents Without Child Records

One common database task is identifying customers who have never placed an order. A LEFT JOIN can be combined with an IS NULL condition to find such records.

SELECT
    c.customer_id,
    c.customer_name
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

This query returns customers for whom no matching order exists. It is useful for customer engagement, reporting, and follow-up campaigns.

13. Counting Child Records for Each Parent

Aggregate functions such as COUNT() can be used to calculate how many child records belong to each parent. When using LEFT JOIN, count a non-NULL child primary key rather than using COUNT(*), because the latter counts the unmatched parent row too.

SELECT
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) AS total_orders
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY total_orders DESC;

This query includes customers with zero orders and displays the number of orders associated with each customer.

14. Calculating the Total Value of Child Records

You can use SUM() to calculate the total amount of orders placed by each customer. Since SUM returns NULL when there are no non-NULL input values, use COALESCE() to display zero when appropriate.

SELECT
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) AS total_orders,
    COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY total_spent DESC;

This is a common example of using one-to-many relationships in business reporting.

15. One-to-Many Relationships in Real-World Applications

These examples are simplified. In a real application, the exact relationship depends on the business rules. For instance, books may have multiple authors, which requires a many-to-many design rather than a simple one-to-many relationship.

16. One-to-Many vs. One-to-One vs. Many-to-Many

The key distinction is where the foreign key is placed and whether the database design allows multiple associations on one or both sides.

17. Referential Integrity and Foreign Key Rules

Referential integrity ensures that relationships between tables remain valid. With a foreign key in place, MySQL can prevent a child record from referencing a parent record that does not exist.

Example: Valid Insert

INSERT INTO orders (customer_id, order_date, total_amount)
VALUES (1, '2026-10-15', 750.00);

This insert succeeds if customer ID 1 exists and the other column constraints are satisfied.

Example: Invalid Insert

INSERT INTO orders (customer_id, order_date, total_amount)
VALUES (9999, '2026-10-15', 750.00);

If customer ID 9999 does not exist, MySQL rejects the insert because the foreign key would reference a nonexistent parent. The exact error message depends on the MySQL version and execution context.

18. ON DELETE and ON UPDATE Actions

Foreign key actions define how MySQL handles a parent key update or parent-row deletion when related child records exist. Choose the action according to the business rules.

Example using cascading deletion:

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT NOT NULL,
    CONSTRAINT fk_employee_department
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE = InnoDB;

With this definition, deleting a department deletes its associated employees, and changing a referenced department ID updates the corresponding foreign key values. Cascading deletion should only be used when deleting dependent records is genuinely required.

19. Optional and Mandatory Relationships

A one-to-many relationship can be mandatory or optional depending on the business requirements and the child foreign key's nullability.

• Mandatory child relationship: The child must reference a parent. Define the foreign key column as NOT NULL.
• Optional child relationship: A child may exist without an assigned parent. Allow the foreign key column to contain NULL.
• Parent with no children: A parent record can have zero child records unless additional business rules require at least one.

For example, if every order must belong to a customer, use customer_id INT NOT NULL. If an employee may temporarily be unassigned to a department, the employee's department foreign key may be nullable.

20. Indexing Foreign Keys

Indexes can improve performance when MySQL searches, joins, or filters records by foreign key values. InnoDB requires suitable indexes for foreign key checks and creates an index on the referencing columns if a suitable index does not already exist.

For a customer-order relationship, the foreign key column is a natural choice for join and lookup operations:

SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = 1;

Before adding extra indexes, inspect the existing table definition and query execution plan. Unnecessary indexes increase storage use and can slow down INSERT, UPDATE, and DELETE operations.

21. Common Mistakes in One-to-Many Relationships

22. Troubleshooting Common Errors

Error 1: Cannot Add or Update a Child Row

Cause: The foreign key value in the child record does not match a valid parent key, or another foreign key requirement is not met.

Solution: Check that the parent record exists, confirm the foreign key value, and verify that the parent and child columns are compatible.

SELECT customer_id
FROM customers
WHERE customer_id = 9999;

Error 2: Cannot Delete or Update a Parent Row

Cause: Related child records exist and the foreign key action prevents the operation.

Solution: Review the relationship requirements. If deletion is permitted, handle the child records appropriately or configure a suitable referential action. Do not remove constraints merely to bypass an error.

Error 3: Foreign Key Constraint Is Incorrectly Formed

Possible causes:

• The referenced table or column does not exist.
• The referenced column is not indexed as required.
• The parent and child column definitions are incompatible.
• The tables use storage engines that do not support the intended foreign key behavior.
• The constraint definition contains a syntax or naming issue.

Inspect the table definitions using:

SHOW CREATE TABLE customers;
SHOW CREATE TABLE orders;

Error 4: Duplicate Parent or Child IDs

Cause: A primary key value has been reused, or a manually assigned ID conflicts with an existing record.

Solution: Use a properly defined primary key and, where suitable, AUTO_INCREMENT. Do not confuse a unique primary key with a foreign key: foreign key values are normally repeated in one-to-many relationships.

23. Best Practices for Designing One-to-Many Relationships

• Identify the parent and child entities before creating tables.
• Give each table a primary key.
• Place the foreign key in the child table.
• Use descriptive names such as customer_id and department_id.
• Make foreign keys NOT NULL when the association is mandatory.
• Use InnoDB for foreign key enforcement in MySQL.
• Choose ON DELETE and ON UPDATE actions according to the business rules.
• Use joins and aggregate functions to retrieve and summarize related data.
• Check indexes and query execution plans for large datasets.
• Test valid and invalid inserts, updates, and deletes.
• Document relationship rules so developers understand how the data is connected.
• Avoid storing multiple related IDs in a single text field.

24. Practical Exercise: Department and Employee Database

Task: Create a department table and an employee table. Each department can have many employees, while each employee belongs to one department.

Step 1: Create the Tables

CREATE DATABASE company_relationships;
USE company_relationships;

CREATE TABLE departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL UNIQUE
) ENGINE = InnoDB;

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL,
    department_id INT NOT NULL,
    CONSTRAINT fk_employees_departments
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
) ENGINE = InnoDB;

Step 2: Insert Sample Data

INSERT INTO departments (department_name)
VALUES ('IT'), ('HR'), ('Finance');

INSERT INTO employees (employee_name, salary, department_id)
VALUES
('Aarav', 45000.00, 1),
('Meera', 52000.00, 1),
('Kabir', 40000.00, 2),
('Ananya', 48000.00, 3),
('Rohan', 47000.00, 1);

Step 3: Display Employees with Their Departments

SELECT
    e.employee_id,
    e.employee_name,
    e.salary,
    d.department_name
FROM employees AS e
INNER JOIN departments AS d
    ON e.department_id = d.department_id;

Step 4: Count Employees by Department

SELECT
    d.department_id,
    d.department_name,
    COUNT(e.employee_id) AS employee_count
FROM departments AS d
LEFT JOIN employees AS e
    ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
ORDER BY d.department_id;

Step 5: Find Departments Without Employees

SELECT
    d.department_id,
    d.department_name
FROM departments AS d
LEFT JOIN employees AS e
    ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;

In this sample dataset, HR has an employee, while the remaining departments also have employees. Therefore, the final query returns no rows. Add a department without an employee to test the query.

Practice Questions

1. Why is department_id repeated in the employees table?
2. What happens when an employee references a department that does not exist?
3. How would you display all departments, including those without employees?
4. How would you calculate the average salary in each department?
5. How would you prevent deletion of a department that still has employees?

25. Interview Questions and Answers

Q1. What is a one-to-many relationship?

A one-to-many relationship allows one record in a parent table to be associated with multiple records in a child table. Each child record references a parent record through a foreign key, unless the foreign key is nullable and the relationship is optional.

Q2. Where is the foreign key placed in a one-to-many relationship?

The foreign key is placed in the child table, where it references the primary key or another eligible referenced key in the parent table.

Q3. Can a foreign key contain duplicate values?

Yes. A foreign key in a one-to-many relationship commonly contains repeated values because several child records can belong to the same parent.

Q4. Can a parent record exist without child records?

Yes. A foreign key does not automatically require every parent record to have at least one child. A parent can exist without related child records unless additional business rules enforce otherwise.

Q5. What is the difference between a primary key and a foreign key?

A primary key uniquely identifies a row in its own table. A foreign key references a key in another table to establish a relationship and enforce referential integrity.

Q6. What is the purpose of a LEFT JOIN?

A LEFT JOIN returns all rows from the left table and matching rows from the right table. When no match exists, columns from the right table contain NULL.

Q7. What is referential integrity?

Referential integrity ensures that a non-NULL foreign key value references a valid record in the parent table and that defined relationship rules are respected.

Q8. What does ON DELETE CASCADE do?

It automatically deletes related child records when the referenced parent record is deleted. It should be used only when this behavior matches the application's data-retention requirements.

Q9. Can a child table have more than one foreign key?

Yes. A child table can reference multiple parent tables. For example, an order item may reference both an order and a product.

Q10. How do you count child records for each parent?

Use a LEFT JOIN with GROUP BY and count the child table's primary key, such as COUNT(o.order_id), so parents with no children receive a count of zero.

26. Quick Revision Checklist

• A one-to-many relationship connects one parent record to multiple child records.
• The parent table normally contains the referenced primary key.
• The child table contains the foreign key.
• Foreign key values can repeat in a one-to-many relationship.
• A foreign key helps enforce referential integrity.
• Use INNER JOIN for matching records and LEFT JOIN when all parent records must be shown.
• Use COUNT(child_primary_key) to count child rows after a LEFT JOIN.
• Use appropriate ON DELETE and ON UPDATE actions.
• Check data types, table definitions, indexes, and constraints when troubleshooting.
• Test relationships with realistic sample data before using them in production.

27. Conclusion

One-to-many relationships are essential to organizing relational data in MySQL. They allow one parent record, such as a customer or department, to be associated with multiple child records, such as orders or employees. By placing a foreign key in the child table, developers can maintain valid connections between records without storing lists of related IDs in a single column.

Understanding parent and child tables, primary keys, foreign keys, joins, aggregate functions, and referential actions helps you create reliable database structures and write useful queries. Practice the customer-order and department-employee examples to strengthen your understanding of one-to-many relationships.

28. Additional Learning Resources

• Explore the MySQL Training Course at JustAcademy
• Register for a MySQL Training Demo
• Visit the JustAcademy Website
• MySQL Official Documentation
• MySQL Foreign Key Constraints Reference